# coding=utf-8

import web
from web.utils import storage

from db import connection

db = connection.default

def add_action_comment(aff_id, user_id, action, comment):
    """
    添加灵感信息的动作附注
    """
    return db.query('''
            INSERT INTO afflatus_action_comment(id,content)
            SELECT id,$comment 
            FROM afflatus_action 
            WHERE afflatus_id = $aff_id AND action_type = $action 
                AND user_id = $user_id
            ON DUPLICATE KEY UPDATE content = $comment;
            ''', vars = locals())

def unbeen_to(aff_id, user_id):
    """
    取消去过
    """
    return unaction(aff_id, user_id, 1)

def been_to(aff_id, user_id, rating, comment):
    """
    去过
    """
    return action(aff_id, user_id, 1, rating, comment)

def all_actioned_types_by_user(aff_id, user_id):
    """
    获取给定用户对给定灵感信息，进行过的所有动作
    """
    sets = db.query('''
                    SELECT action_type 
                    FROM afflatus_action 
                    WHERE afflatus_id = $aff_id
                        AND user_id = $user_id
                    GROUP BY action_type;
                    '''
                    , vars = locals())
    if sets:
        return [row.action_type for row in sets]
    
    return None

def actioned(aff_id, user_id, action):
    """
    获取给定用户(@user_id)是否对给定灵感信息(@aff_id)
    进行了给定的操作(@action)
    """
    sets = db.query('''SELECT EXISTS(SELECT NULL
                                     FROM afflatus_action 
                                     WHERE afflatus_id = $aff_id 
                                        AND action_type = $action 
                                        AND user_id = $user_id
                                    ) AS ext;'''
                                , vars = locals())
    return sets[0].ext == 1

def unaction(aff_id, user_id, action):
    """
    取消动作记录
    """
    if aff_id <= 0 or user_id <= 0:
        return False

    with db.transaction():
        db.query('''
                DELETE FROM afflatus_action_comment 
                WHERE id = (SELECT id 
                            FROM afflatus_action 
                            WHERE afflatus_id = $aff_id 
                                AND action_type = $action
                                AND user_id = $user_id);
                ''', vars = locals())
        return db.delete('afflatus_action'
                    , where = '''
                            afflatus_id = $aff_id 
                            AND action_type = $action
                            AND user_id = $user_id
                        '''
                    , vars = locals())

def action(aff_id, user_id, action, rating = 0, comment = ''):
    """
    保存对灵感信息的动作信息

    @aff_id as int, 灵感内码
    @user_id as int, 用户内码
    @action as int, 动作类型
    @rating as int, 评价 0-5【可选】
    @comment as string, 附注【可选】
    """
    if aff_id <= 0 or user_id <= 0:
        return False

    with db.transaction():
        db.query('''
                INSERT IGNORE INTO afflatus_action 
                VALUES(NULL,$aff_id,$user_id,$action,$rating,NOW());
                '''
                , vars = locals())
        # 获取最后一次插入的id
        sets = db.select('afflatus_action'
                , what = 'id'
                , where = '''
                        afflatus_id = $aff_id 
                        AND action_type = $action 
                        AND user_id = $user_id
                        '''
                , vars = locals())
        if sets:
            id = sets[0].id
            # id有效，且评论有效
            if id > 0 and comment:
                db.insert('afflatus_action_comment',
                        id = id,
                        content = comment)

            return id
    
    return False

def followed(aff_id, user_id):
    """
    指定用户是否关注了指定灵感信息
    """
    return actioned(aff_id, user_id, 0)

def unfollow(aff_id, user_id):
    """
    指定用户取消对指定灵感信息的关注
    """
    return unaction(aff_id, user_id, 0)

def follow(aff_id, user_id):
    """
    指定用户关注指定的灵感信息
    """
    return action(aff_id, user_id, 0)
    return db.insert('afflatus_action'
            , afflatus_id = aff_id
            , action_type = 0
            , user_id = user_id
            , date_created = web.SQLLiteral('NOW()'))

def get_afflatus_adjacent_ids(id):
    """
    获取给定id的上一条及下一条灵感信息的id
    returns:
        0 - Prev
        1 - Next
    """
    sets = db.query('''
            SELECT
                CASE 
                    WHEN SIGN(ID - $id) > 0 THEN 'next' 
                    ELSE 'prev' 
                END AS dir, 
                CASE 
                    WHEN SIGN(id - $id) > 0 THEN MIN(id) 
                    WHEN SIGN(id - $id) < 0 THEN MAX(id) 
                END AS id 
            FROM afflatus 
            WHERE id <> $id 
            GROUP BY SIGN(id - $id) 
            ORDER BY SIGN(id - $id);
            '''
            , vars = locals())
    ret = storage(next = 0, prev = 0)
    if sets:
        for row in sets:
            ret[row.dir] = row.id

    return ret

def get_related_afflatus(limit, id):
    """
    获取与给定信息关联的灵感信息
    """
    return db.query('''
        SELECT * FROM `afflatus`
        WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `afflatus`)-(SELECT MIN(id) FROM `afflatus`)) + (SELECT MIN(id) FROM `afflatus`)))
        ORDER BY id LIMIT $limit;
            '''
            , vars = locals())

def get_afflatus_by_id(id):
    """
    获取给定灵感信息
    """
    sets = db.query('''
            SELECT a.id,a.title,a.short_text,
                    a.media_id,dest.name AS dest_name, 
                    tp.type
            FROM afflatus AS a
                INNER JOIN
                travel_proj AS tp
                    ON tp.id = a.proj_id
                INNER JOIN
                travel_dest As dest
                    ON dest.id = tp.dest_id
            WHERE a.id = $id
            ''', vars = locals())
    if sets:
        return sets[0]
    return None

def get_all_afflatus_rand(length, excludes):
    """
    随机分页读取灵感信息
    @length as int, 读取总量
    @excludes as string, 要排除的灵感信息id列表（半角逗号分割）

    returns:
        list
    """
    import random
    # 读取一系列有效id出来
    idlst = get_all_afflatus_idstr(excludes)
    if idlst:
        # 获取随机的 length 个 id
        idlst = random.sample(idlst, min(length, len(idlst)))
        # 获取列表
        lst = [row for row in get_afflatus_by_ids(idlst)]
        # 因为这个列表是按id有序排列的
        # 所以这里为了增加随机性
        length = len(lst)
        lst = random.sample(lst, length)
        return storage(result = lst, totalrecords = length)

    # OVER.
    return None

def get_afflatus_by_ids(idlst):
    """
    获取给定的id列表中的灵感信息
    """
    if isinstance(idlst, list):
        idlst = ','.join(idlst)

    sets = db.query('''
            SELECT a.id,a.title,a.short_text,
                    a.media_id,dest.name AS dest_name 
            FROM afflatus AS a
                INNER JOIN
                travel_proj AS tp
                    ON tp.id = a.proj_id 
                INNER JOIN
                travel_dest AS dest
                    ON dest.id = tp.dest_id
            WHERE a.id IN (%s);
            ''' % idlst)

    return sets

def get_all_afflatus_idstr(excludes):
    """
    获取所有灵感信息的id
    """
    if excludes:
        excludes = ' WHERE id NOT IN (%s)' % excludes
    else:
        excludes = ''

    return [str(row.id) for row in db.query('SELECT id FROM afflatus%s LIMIT 100;' % excludes)]

def get_all_afflatus(offset, length, include_totalrecords = False):
    """
    分页读取灵感信息
    @offset as int, 读取偏移量
    @length as int, 一次读取的记录数
    @include_totalrecords as bool, 是否包含总记录数
    """
    pager_sql = ''
    if include_totalrecords:
        pager_sql = 'SQL_CALC_FOUND_ROWS'

    sql = '''SELECT %s a.id,a.title,a.short_text,
                    a.media_id,dest.name AS dest_name 
             FROM afflatus AS a
                INNER JOIN
                travel_proj AS tp
                    ON tp.id = a.proj_id 
                INNER JOIN
                travel_dest AS dest
                    ON dest.id = tp.dest_id 
             ORDER BY a.id DESC 
             LIMIT $offset, $length;''' % pager_sql
    sets = db.query(sql, vars = locals())
    # 总记录数
    # 只在必要是计算并返回
    totalrecords = len(sets)
    if include_totalrecords:
        totals = db.query('SELECT FOUND_ROWS() AS totals;')
        totalrecords = totals[0].totals

    return storage(result = sets, totalrecords = totalrecords)
